<?php

namespace app\advertisement\logic;

use app\advertisement\model\PerformanceReport as PerformanceReportModel;
use think\facade\Db;
use app\data\util\HttpUtil;
use ba\Exception;
use think\facade\Config;
use think\facade\Cache;
use app\common\library\Excel;

class PerformanceReportLogic
{
    protected $model = null;

    public function __construct()
    {
        $this->model = new PerformanceReportModel();
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 列表
     * @author zhangheng
     */
    public function index($params = [])
    {
        $stat_time_day = $params['stat_time_day'] ?? '';
        $company = $params['company'] ?? '';
        $limit = $params['limit'] ?? 10;
        
        $order = $params['order'] ?? 'id,desc';
        if($order = "profit,desc"){
            $order = "id,desc";
        }
        
        $where = [];
        if (!empty($stat_time_day)) {
            $createtimeArr = explode(',', $stat_time_day);
            $where[] = ['stat_time_day', '>=', strtotime($createtimeArr[0])];
            $where[] = ['stat_time_day', '<=', strtotime($createtimeArr[1])];
            $display_day = strtotime($createtimeArr[1]);
        }else{
            $where[] = ['stat_time_day', '=', strtotime(date('Y-m-d',time() - 16 * 3600))];
            $display_day = strtotime(date('Y-m-d',time() - 16 * 3600));
        }
        if (!empty($company)) {
            $where[] = ['company', '=', $company];
        }
        
        //排序
        list($field, $direction) = explode(',', $order);  
        $field = trim($field);  
        $direction = trim($direction);  
        $order = [$field => $direction];  
        $list = Db::name('advertising_report')
            ->field('name,company,sum(real_time_conversion) as real_time_conversion,sum(conversion) as conversion,sum(spend) as spend,sum(total_onsite_shopping_value) as total_onsite_shopping_value')
            ->where($where)
            ->where("conversion","<>",0)
            ->group("name,company")
            ->order($order)
            ->paginate($limit);
        $lists = $list->items();

        $profits = 0;
        $profits_new = 0;
        $RealTimeRevenue_new = 0;
        //预估值
        $exchange = Db::name('exchange')
            ->where('date',date('Y-m',$display_day))
            ->order('date desc')
            ->find();
            
        foreach ($lists as $k=>$v) {
            if($v['total_onsite_shopping_value'] >= 0.001 && $v['spend'] >= 0.001){
                $lists[$k]['onsite_shopping_roas'] = number_format($v['total_onsite_shopping_value'] / $v['spend'], 2, '.', '');
            }else{
                $lists[$k]['onsite_shopping_roas'] = 0;
            }
            $lists[$k]['stat_time_day'] = $display_day;

            //新增公式
            // 收入*（1-退款率-手续费率）  - 花费 - 采购成本/7.106 - 头程/7.106 - 海外仓成本合计
            // 海外仓成本合计 = 入库费 + 订单处理费 + 分拣费 + 包装费 + 海外仓配送费
            //件数
            $NumberOfPieces = number_format($v['conversion']*1.5, 1, '.', '');
            //入库费
            $StorageFee = number_format(0.082*$NumberOfPieces, 3, '.', '');
            //订单处理费
            $OrderProcessingFee = number_format(0.5*$v['conversion'], 3, '.', '');
            //分拣费
            $SortingFee = number_format(0.5*$NumberOfPieces, 3, '.', '');
            //包装费
            $PackagingFee = number_format(0.15*$v['conversion']/2, 3, '.', '');
            //海外仓配送费
            $OverseasWarehouseDeliveryFee = number_format(6.28*$v['conversion'], 3, '.', '');
            //海外仓成本合计
            $TotalCostOfOverseasWarehouse = number_format($StorageFee + $OrderProcessingFee + $SortingFee + $PackagingFee + $OverseasWarehouseDeliveryFee, 3, '.', '');

            //头程
            $CephalicProcess = number_format($NumberOfPieces* 14.48/$exchange['proportion'], 3, '.', '');
            //采购成本
            $ProcurementCost = number_format($NumberOfPieces* 22.98/$exchange['proportion'], 3, '.', '');
            //利率
            $InterestRate = number_format(1-$exchange['refund_fee']/100-$exchange['platform_fee']/100, 3, '.', '');
            //收入
            $Income = number_format($v['total_onsite_shopping_value']*$InterestRate, 3, '.', '');
            //盈利
            $profit = number_format($Income - $v['spend'] - $CephalicProcess - $ProcurementCost - $TotalCostOfOverseasWarehouse, 3, '.', '');
            $lists[$k]['profit'] = $profit;
            $profits = number_format($profits + $profit, 3, '.', '');

            //实时收入
            $RealTimeRevenue = 0;
            if($v['conversion'] > 0.001 && $v['real_time_conversion'] > 0.001){
                $RealTimeRevenue = number_format($v['total_onsite_shopping_value'] / $v['conversion'] * $v['real_time_conversion'], 3, '.', '');
            }
            $lists[$k]['RealTimeRevenue'] = $RealTimeRevenue;
            //实时ROI
            $RealTimeRoi = 0;
            if($RealTimeRevenue > 0.001 && $v['spend'] > 0.001){
                $RealTimeRoi = number_format($RealTimeRevenue / $v['spend'], 3, '.', '');
            }
            $lists[$k]['RealTimeRoi'] = $RealTimeRoi;
            //实时盈利开始
            //件数
            $NumberOfPieces_new = number_format($v['real_time_conversion']*1.5, 1, '.', '');
            //入库费
            $StorageFee = number_format(0.082*$NumberOfPieces_new, 3, '.', '');
            //订单处理费
            $OrderProcessingFee = number_format(0.5*$v['real_time_conversion'], 3, '.', '');
            //分拣费
            $SortingFee = number_format(0.5*$NumberOfPieces_new, 3, '.', '');
            //包装费
            $PackagingFee = number_format(0.15*$v['real_time_conversion']/2, 3, '.', '');
            //海外仓配送费
            $OverseasWarehouseDeliveryFee = number_format(6.28*$v['real_time_conversion'], 3, '.', '');
            //海外仓成本合计
            $TotalCostOfOverseasWarehouse = number_format($StorageFee + $OrderProcessingFee + $SortingFee + $PackagingFee + $OverseasWarehouseDeliveryFee, 3, '.', '');

            //头程
            $CephalicProcess = number_format($NumberOfPieces_new* 14.48/$exchange['proportion'], 3, '.', '');
            //采购成本
            $ProcurementCost = number_format($NumberOfPieces_new* 22.98/$exchange['proportion'], 3, '.', '');
            //盈利结束
            //收入
            $profit_new = number_format($lists[$k]['RealTimeRevenue']*$InterestRate - $v['spend'] - $CephalicProcess - $ProcurementCost - $TotalCostOfOverseasWarehouse, 3, '.', '');
            $lists[$k]['profit_new'] = $profit_new;
            $profits_new = number_format($profits_new + $profit_new, 3, '.', '');

            //统计收入
            $RealTimeRevenue_new = number_format($RealTimeRevenue_new + $lists[$k]['RealTimeRevenue'], 3, '.', '');
            
        }
        
        $sum = Db::name('advertising_report')
            ->field('sum(spend) as spend,sum(total_onsite_shopping_value) as total_onsite_shopping_value,sum(conversion) as conversion,AVG(onsite_shopping_roas) as onsite_shopping_roas,sum(real_time_conversion) as real_time_conversion')
            ->where($where)
            ->where("conversion","<>",0)
            ->order('id desc')
            ->find();
        if($sum['spend'] >= 0.001 && $sum['total_onsite_shopping_value'] >= 0.001){
            $onsite_shopping_roas = number_format($sum['total_onsite_shopping_value'] / $sum['spend'], 2, '.', '');
        }else{
            $onsite_shopping_roas = 0;
        }

        if($sum['spend'] >= 0.001 && $RealTimeRevenue_new >= 0.001){
            $roi_new = number_format($RealTimeRevenue_new / $sum['spend'], 2, '.', '');
        }else{
            $roi_new = 0;
        }
        
        $data = [
            'list' => $lists,
            'total' => $list->total(),
            'spend' => !empty($sum['spend']) ? number_format($sum['spend'], 2, '.', '') : 0,
            'total_onsite_shopping_value' => !empty($sum['total_onsite_shopping_value']) ? number_format($sum['total_onsite_shopping_value'], 2, '.', '') : 0,
            'conversion' => !empty($sum['conversion']) ? number_format($sum['conversion'], 2, '.', '') : 0,
            'real_time_conversion' => !empty($sum['real_time_conversion']) ? number_format($sum['real_time_conversion'], 2, '.', '') : 0,
            'onsite_shopping_roas' => $onsite_shopping_roas,
            'profits' => $profits,
            'profits_new' => $profits_new,
            'RealTimeRevenue_new' => $RealTimeRevenue_new,
            'roi_new' => $roi_new,
        ];
        return json(DefaultResponse(200, true, '获取成功', $data));
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 导出
     * @author zhangheng
     */
    public function export($params = [])
    {
        $stat_time_day = $params['stat_time_day'] ?? '';

        $where = [];
        if (!empty($stat_time_day)) {
            $createtimeArr = explode(',', $stat_time_day);
            $where[] = ['stat_time_day', '>=', strtotime($createtimeArr[0])];
            $where[] = ['stat_time_day', '<=', strtotime($createtimeArr[1])];
        }

        $list = Db::name('advertising_report')
            ->where($where)
            ->select()
            ->toarray();
        foreach ($list as $k=>$v) {
            $list[$k]['stat_time_day'] = date('Y-m-d', $v['stat_time_day']);
        }
        
        $header[] = ['姓名', 'name'];
        $header[] = ['花费', 'spend'];
        $header[] = ['收入', 'total_onsite_shopping_value'];
        $header[] = ['订单', 'conversion'];
        $header[] = ['ROI', 'onsite_shopping_roas'];
        $header[] = ['时间', 'stat_time_day'];
        $fileName = "export_advaccount" . time();
        return Excel::exportData($list, $header, $fileName, 'xlsx');
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 列表
     * @author zhangheng
     */
    public function info($params = [])
    {
        $stat_time_day = $params['stat_time_day'] ?? '';
        $name = $params['name'] ?? '';
        $limit = $params['limit'] ?? 10;
        $where = [];
        //总消耗不为0
        // $where[] = ['a.spend', '>=', 0.001];

        if (!empty($stat_time_day)) {
            $createtimeArr = explode(',', $stat_time_day);
            $where[] = ['a.stat_time', '>=', strtotime($createtimeArr[0])];
            $where[] = ['a.stat_time', '<=', strtotime($createtimeArr[1])];
        }else{
            $where[] = ['a.stat_time', '>=', strtotime(date('Y-m-d',time() - 16 * 3600))];
            $where[] = ['a.stat_time', '<=', strtotime(date('Y-m-d',time() - 16 * 3600))];
        }

        if (!empty($name)) {
            $config = Config::get('Logisticskey.' . 'PerformanceRulesForAdvertising');
            foreach ($config as $campaign) {
                if (strpos($name, $campaign[0]) !== false) {
                    $campaignKey = $campaign[2];
                    break;
                }
            }
        }
        if (isset($campaignKey)) {
            $where[] = ['a.campaign_name', 'like', '%' . $campaignKey . '%'];
        }

        $list = Db::name('advertising_campaign')
            ->field('a.*,b.advertiser_name')
            ->alias('a')
            ->join('ba_advaccount b', 'b.advertiser_id = a.advertiser_id', 'left')
            ->where($where)
            ->order('a.stat_time desc,a.spend desc')
            ->paginate($limit);
            
        $lists = $list->items();

        $data = [
            'list' => $lists,
            'total' => $list->total(),
        ];
        return json(DefaultResponse(200, true, '获取成功', $data));
    }

}
